﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace Utility
{
    public class Sql
    {
        #region Sql注入过滤
        /// <summary>
        ///  Sql注入过滤
        /// </summary>
        /// <param name="_Val"></param>
        /// <returns></returns>
        public static string SqlFilter(string _Val)
        {
            if (_Val == null || _Val == "") { return ""; }
            string Keys = "and|exec|insert|select|delete|update|chr|mid|master|or|truncate|char|declare|join";
            foreach (string S in Keys.Split('|'))
            {
                _Val.Replace(S, "[" + S + "]");
            }
            _Val = _Val.Replace("'", "\"");
            return _Val;
        }
        #endregion

        public static string connstr { get; set; }
        public int RecordCount = 0;
        public Sql()
        {
        }

        #region 静态方法：运行Sql语句，返回DataReader记录集(应用此方法一定记得关闭DataReader)
        /// <summary>
        /// 静态方法：运行Sql语句，返回DataReader记录集(应用此方法一定记得关闭DataReader)
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <returns></returns>
        public static SqlDataReader ReturnDr_Static(string sql)
        {
            SqlConnection conn = new SqlConnection(connstr);
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataReader dr = null;

            try
            {
                conn.Open();
                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            return dr;
        }
        #endregion

        #region 静态方法：执行查询，返回DataSet
        /// <summary>
        /// 静态方法：执行查询，返回DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet ReturnDs_Static(string sql)
        {
            SqlConnection conn = new SqlConnection(connstr);
            DataSet ds = new DataSet();

            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds);

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                CloseConn(conn);
            }
            return ds;
        }

        #endregion

        #region 返回DataSet记录集，用于AspNetPager控件的翻页
        /// <summary>
        /// 返回DataSet记录集，用于AspNetPager控件的翻页
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="StartIndex">开始的记录序号</param>
        /// <param name="EndIndex">读取记录的条数</param>
        /// <param name="TableName">映射的表名</param>
        /// <returns></returns>
        public DataSet ReturnDs(string sql, int StartIndex, int PageSize, string TableName)
        {
            SqlConnection conn = new SqlConnection(connstr);
            DataSet ds = new DataSet();
            DataSet dsall = new DataSet();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(dsall);
                da.Fill(ds, StartIndex, PageSize, TableName);
                RecordCount = dsall.Tables[0].Rows.Count;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                CloseConn(conn);
            }
            return ds;
        }
        #endregion

        #region 静态方法：运行Sql语句，返回所影响的行数
        /// <summary>
        /// 静态方法：运行Sql语句，返回所影响的行数
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <returns></returns>
        public static int Returncmd_Static(string sql)
        {
            SqlConnection conn = new SqlConnection(connstr);
            SqlCommand comm = new SqlCommand(sql, conn);

            try
            {
                conn.Open();
                return comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                CloseConn(conn);
            }
        }
        #endregion

        #region 静态方法：数据库链接的关闭
        /// <summary>
        /// 静态方法：数据库链接的关闭
        /// </summary>
        /// <param name="conn"></param>
        public static void CloseConn(SqlConnection conn)
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
        #endregion

    }
}
